'''
本程序进行 pandas
'''
import pandas as pd
import numpy as np
df = pd.DataFrame({'Gender':['F','F','M','M'],
              'Height':[163, 160, 175, 180]})
print(df)
df2 = pd.DataFrame({'Height: F':[163, 160],
              'Height: M':[175, 180]})

df = pd.DataFrame({'Class':[1,1,2,2],
                   'Name':['San Zhang','San Zhang','Si Li','Si Li'],
                   'Subject':['Chinese','Math','Chinese','Math'],
                   'Grade':[80,75,90,85]})
'''
pivolt 是 长表别换成短表，进行行列转换
'''
width = df.pivot(index='Name', columns='Subject', values='Grade')
print(width)

df = pd.DataFrame({'Class':[1, 1, 2, 2, 1, 1, 2, 2],
                  'Name':['San Zhang', 'San Zhang', 'Si Li', 'Si Li',
                           'San Zhang', 'San Zhang', 'Si Li', 'Si Li'],
                  'Examination': ['Mid', 'Final', 'Mid', 'Final',
                                 'Mid', 'Final', 'Mid', 'Final'],
                  'Subject':['Chinese', 'Chinese', 'Chinese', 'Chinese',
                              'Math', 'Math', 'Math', 'Math'],
                  'Grade':[80, 75, 85, 65, 90, 85, 92, 88],
                  'rank':[10, 15, 21, 15, 20, 7, 6, 2]})
'''
满足于条件唯一的时候可以使用
'''
pivot_multi = df.pivot(index = ['Class', 'Name'],
                       columns = ['Subject','Examination'],
                       values = ['Grade','rank'])
'''
针对条件不唯一的情况可以使用pivot_table
'''
df = pd.DataFrame({'Name':['San Zhang', 'San Zhang',
                           'San Zhang', 'San Zhang',
                           'Si Li', 'Si Li', 'Si Li', 'Si Li'],
                  'Subject':['Chinese', 'Chinese', 'Math', 'Math',
                              'Chinese', 'Chinese', 'Math', 'Math'],
                  'Grade':[80, 90, 100, 90, 70, 80, 85, 95]})
'''
上述情况可以使用pivot_table 并结合聚合函数进行处理
'''
df.pivot_table(index = 'Name',
               columns = 'Subject',
               values = 'Grade',
               aggfunc = 'mean')
df.pivot_table(index = 'Name',
               columns = 'Subject',
               values = 'Grade',
               aggfunc = lambda x:x.mean())
'''
针对多列的数据汇总进行边际汇总（求多个统计值得汇总数据，求均值或者其他）
'''
df.pivot_table(index = 'Name',
               columns = 'Subject',
               values = 'Grade',
               aggfunc='mean',
               margins=True)
df = pd.DataFrame({'Class':[1,2],
                  'Name':['San Zhang', 'Si Li'],
                  'Chinese':[80, 90],
                  'Math':[80, 75]})
'''
与pilot相反进行短表变长表
'''
df_melted = df.melt(id_vars = ['Class', 'Name'],
                    value_vars = ['Chinese', 'Math'],
                    var_name = 'Subject',
                    value_name = 'Grade')

df_unmelted = df_melted.pivot(index = ['Class', 'Name'],
                              columns='Subject',
                              values='Grade')
df_unmelted = df_unmelted.reset_index().rename_axis(
                             columns={'Subject':''})
'''
交叉汇总压缩需要wide_to_long
'''
df = pd.DataFrame({'Class':[1,2],'Name':['San Zhang', 'Si Li'],
                   'Chinese_Mid':[80, 75], 'Math_Mid':[90, 85],
                   'Chinese_Final':[80, 75], 'Math_Final':[90, 85]})
pd.wide_to_long(df,
                stubnames=['Chinese', 'Math'],
                i = ['Class', 'Name'],
                j='Examination',
                sep='_',
                suffix='.+')

res = pivot_multi.copy()

res.columns = res.columns.map(lambda x:'_'.join(x))

res = res.reset_index()

res = pd.wide_to_long(res, stubnames=['Grade', 'rank'],
                           i = ['Class', 'Name'],
                           j = 'Subject_Examination',
                           sep = '_',
                           suffix = '.+')

res = res.reset_index()

res[['Subject', 'Examination']] = res[
                'Subject_Examination'].str.split('_', expand=True)


res = res[['Class', 'Name', 'Examination',
           'Subject', 'Grade', 'rank']].sort_values('Subject')


res = res.reset_index(drop=True)
df = pd.DataFrame(np.ones((4,2)),
                  index = pd.Index([('A', 'cat', 'big'),
                                    ('A', 'dog', 'small'),
                                    ('B', 'cat', 'big'),
                                    ('B', 'dog', 'small')]),
                  columns=['col_1', 'col_2'])
# 转换最内层的索引 unstack  必须保证唯一
df.unstack(2)
df.unstack([0,2])

my_index = df.index.to_list()
# 与 unstack 相反， stack 的作用就是把列索引的层压入行索引，其用法完全类似
df = pd.DataFrame(np.ones((4,2)),
                  index = pd.Index([('A', 'cat', 'big'),
                                    ('A', 'dog', 'small'),
                                    ('B', 'cat', 'big'),
                                    ('B', 'dog', 'small')]),
                  columns=['index_1', 'index_2']).T
df.stack([1, 2])
# crosstab 统计函数
df = pd.read_csv('data/learn_pandas.csv')

'''
三种方式进行统计
'''
pd.crosstab(index = df.School, columns = df.Transfer)
pd.crosstab(index = df.School, columns = df.Transfer,
            values = [0]*df.shape[0], aggfunc = 'count')
df.pivot_table(index = 'School',
               columns = 'Transfer',
               values = 'Name',
               aggfunc = 'count')
# 组合求均值
pd.crosstab(index = df.School, columns = df.Transfer,
            values = df.Height, aggfunc = 'mean')
#
df_ex = pd.DataFrame({'A': [[1, 2],
                         'my_str',
                         {1, 2},
                         pd.Series([3, 4])],
                      'B': 1})
# 展开某一列
df_ex.explode('A')
# 根据值进行转换成columns
pd.get_dummies(df.Grade).head()